﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;


namespace TestJQGrid
{
    public partial class RuleExecutionResult : System.Web.UI.Page
    {
        int RuleID, CycleID;
        DataTable salesdata;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                RuleID = Convert.ToInt32(Request.QueryString["RuleID"].ToString());
                CycleID = Convert.ToInt32(Request.QueryString["CycleID"].ToString());
                salesdata = new DataTable();

                if (RuleID > 0)
                {
                    retrieveDetails(RuleID, CycleID);
                    //fill_result(RuleID);
                }
            }
        }

        private void retrieveDetails(int rule, int cycle)
        {
            DataTable subrule = getSubRules(rule);
            DateTime[] dateRange = getDateRange(rule, cycle);
            getSalesData(dateRange);

            foreach (DataRow dr in subrule.Rows)
            {
                int subRuleId = Convert.ToInt32(dr[0]);
                String selectQ = "select [WhenPart],[ThenPart] from [FormulaTable] where [id] = '" + subRuleId + "'";
                DataTable completeQuery = GlobalMethods.ExecuteSelectQuery(selectQ);
                string whenpart = completeQuery.Rows[0][0].ToString();
                string thenpart = completeQuery.Rows[0][1].ToString();
                whenpart = generateSqlQuery(whenpart);
                thenpart = generateSqlQuery(thenpart);
                string frompart = "";

                string query = "select " + thenpart + " from " + frompart + " where " + whenpart;
            }
        }

        private string generateSqlQuery(string input)
        {
            string output = input.Replace("@", " ").Replace("\r\n", "").Replace("\"", "'");
            string key_value = "select ('$' + FormulaName + '()') as name , ThenPart from FormulaTable ft where ft.FormulaType='Formula'";
            DataTable searchnreplace = GlobalMethods.ExecuteSelectQuery(key_value);

            bool found;
            do
            {
                found = false;
                for ( int i = 0; i<searchnreplace.Rows.Count; ++i)
                //foreach (DataRow dr in searchnreplace.Rows)
                {
                    DataRow dr = searchnreplace.Rows[i];
                    if (output.Contains(dr[0].ToString()))
                    {
                        output = output.Replace(dr[0].ToString(), dr[1].ToString());
                        output = output.Replace("@", " ").Replace("\r\n", "").Replace("\"", "'"); // for recursive replacements
                        found = true;
                    }
                }
            } while (found);

            //for sql defined functions
            output = output.Replace("$", " ");
            return output;
        }

        private void getSalesData(DateTime[] dates)
        {
            string selectQuery = "SELECT [TransId], [EntryStatus], [AgentId], [TransDate], [CarValue], [CityCode], [Model], ";
            selectQuery += " [Manufacturer], [Scheme], [Description], [IntRate], [CarType], [LoanAmount], [Tenure] FROM [CarSalesData] ";
            selectQuery += " where [EntryStatus] not in ('Rejected','Adjusted')";
            salesdata = GlobalMethods.ExecuteSelectQuery(selectQuery);
        }

        private DataTable getSubRules(int rule)
        {
            DataTable subRuleList = new DataTable();

            string selectQ = "select FormulaTable.ID, FormulaTable.FormulaName from FormulaTable, RuleRelations ";
            selectQ += "where RuleRelations.SubRuleId = FormulaTable.ID AND RuleRelations.RuleId = '" + rule.ToString() + "' ORDER BY RuleRelations.Priority";
            subRuleList = GlobalMethods.ExecuteSelectQuery(selectQ);

            return subRuleList;
        }

        private DateTime[] getDateRange(int rule, int cycle)
        {
            //retrieve dates
            string selectQ = " select (CONVERT(varchar,StartDate,01) + ',' + CONVERT(varchar,StartDate,01)) as dates from CycleStatus  ";
            selectQ += "where RuleId='" + rule + "' and CycleId='" + cycle + "'";
            string[] cycledate = GlobalMethods.ExecuteScalar(selectQ).Split(',');

            //validate and convert to array
            DateTime[] dateRange = new DateTime[2];
            dateRange[0] = Convert.ToDateTime(cycledate[0]);
            dateRange[1] = Convert.ToDateTime(cycledate[1]);

            return dateRange;
        }

        private void fill_result(int rid)
        {
            DataTable dt = new DataTable();

            string query = "select rp.Comission,rp.CreatedOn,rd.RuleName,rd.RuleType,ag.AgentName,cs.StartDate,cs.EndDate from RulePayout rp inner join RuleDetails rd ";
            query += "on rp.RuleId=rd.RuleID inner join Agents ag on rp.AgentId=ag.AgentId inner join CycleStatus cs ";
            query += "on rp.CycleId=cs.CycleId where rp.RuleId=" + RuleID + " and cs.CycleId=" + CycleID + " order by AgentName";


            dt = GlobalMethods.ExecuteSelectQuery(query);

            if (dt != null && dt.Rows.Count > 0)
            {
                gv_result.DataSource = dt;
                gv_result.DataBind();

                lblCycle.Text = Convert.ToDateTime(dt.Rows[0]["StartDate"].ToString()).ToString("dd-MMM-yyyy") + "  " + Convert.ToDateTime(dt.Rows[0]["EndDate"].ToString()).ToString("dd-MMM-yyyy");
                lblruleName.Text = dt.Rows[0]["RuleName"].ToString();
                lblruleType.Text = dt.Rows[0]["RuleType"].ToString();
            }
            else
            {
                lblCycle.Text = "";
                lblruleName.Text = "";
                lblruleType.Text = "";

                btn_Export.Visible = false;

            }
        }
    }
}